# -*- codeing = utf-8 -*-
from bs4 import BeautifulSoup  # 网页解析，获取数据
import urllib.request, urllib.error  # 制定URL，获取网页数据
import pymysql # mysql数据库

mysqlDB = pymysql.connect(host="localhost", port=3306, user="root", password="xiaoyan159", database="test")
cursor = mysqlDB.cursor()

# 定义抓取页面的主方法
# startPage为抓取的其实页， endPage为抓取的结束页
def main(startPage, endPage):
	# 定义抓取的起始页
	startUrl = "https://learnblockchain.cn/categories/all/featured?page={num}"
	for page in range(startPage, endPage+1):
		# 获取到指定url的html源码
		html = getUrlHtml(startUrl.replace("{num}", str(page)))
		# 调用Jsoup对html源码解析，获取需要的标题内容数据
		titleBlockList = getTitleBlock(html)
		# 使用正则表达式解析标题代码块内的内容，并且将数据保存在对象中
		for item in titleBlockList:
			# 使用dict记录需要被保存的数据
			dataDict = {}
			# 获取h2标签内容，从中解析标题内容及链接
			titleSoup = BeautifulSoup(str(item), "html5lib")

			# 在代码块中获取h2的标签，即为标题标签，可以获取到标题的文本内容和链接
			titleTag = titleSoup.find(name="h2", class_="title")
			# 获取titleTag中的超链接a标签，获取链接地址和标题文本
			title = titleTag.find(name="a")
			dataDict["title"] = title.string
			dataDict["href"] = title["href"]

			# 从item里获取标题中的描述性文字
			descriptionTag = titleSoup.find(name="p")
			if descriptionTag != None:
				dataDict["description"] = descriptionTag.string

			# 从item里获取作者信息
			authorTag = titleSoup.find(name="ul", class_="author")
			if authorTag!=None:
				dataDict["author"] = authorTag.find(name="a").text

			# 将map数据保存到mysql数据库中
			saveIntoMySql(dataDict)


# 得到指定一个URL的网页内容
def getUrlHtml(url):
	request = urllib.request.Request(url)
	html = ""
	try:
		response = urllib.request.urlopen(request)
		html = response.read().decode("utf-8")
	except urllib.error.URLError as e:
		if hasattr(e, "code"):
			print(e.code)
		if hasattr(e, "reason"):
			print(e.reason)
	return html

# 获取标题列表页面中所有的标题代码块
def getTitleBlock(html):
	beautifulSoup = BeautifulSoup(html, features="html.parser")
	# 定义一个数组，保存所有的标题内容
	resultSet =  beautifulSoup.find_all('section', class_="stream-list-item")  # 查找符合要求的字符串
	return resultSet

# 将数据保存到MySql数据库中
def saveIntoMySql(dataDict):
	# 定义插入数据库的语句
	sqlstr = '''INSERT INTO test.learnblockchain 
	(title, href, description, author) 
	VALUES 
	("{title}", "{href}", "{description}", "{author}")
				'''
	# 格式化sql语句，将数据内容替换到sql语句中
	try:
		sqlstr = sqlstr.format(title=dataDict['title'].strip(), href=dataDict['href'].strip(), description=dataDict['description'].strip(), author=dataDict['author'].strip())
		result = cursor.execute(sqlstr)
		mysqlDB.commit()
		# 执行插入操作
		print("插入结果：", result)
	except BaseException as e:
		print(e)

# 初始化MySql数据库，建表
def initMySql():
	# 打开数据库连接
	createTableResult = cursor.execute('''
		CREATE TABLE IF NOT EXISTS test.learnblockchain(`id` INT AUTO_INCREMENT ,
	   `title` VARCHAR(300) NOT NULL,
	   `href` VARCHAR(300) NOT NULL,
	   `description` VARCHAR(300) NOT NULL,
	   `author` VARCHAR(50) NOT NULL,
	   PRIMARY KEY (`id`)) 
	   default charset = utf8;
	  ''')
	mysqlDB.commit()
	return createTableResult,mysqlDB

if __name__ == "__main__":  # 当程序执行时
	initMySql()
	# 调用函数
	main(1, 2)
	print("爬取完毕！")
	if mysqlDB != None:
		mysqlDB.close()
	# try:
	# 	initMySql()
	# 	# 调用函数
	# 	main(1, 2)
	# 	print("爬取完毕！")
	# except BaseException as e:
	# 	print("出现异常", e)
	# finally:
	# 	if mysqlDB != None:
	# 		pymysql.Connection(mysqlDB).close()